package vt.transit.server.data.access;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import vt.transit.server.data.MetroStop;
import vt.transit.server.data.MetroStopCrime;
import vt.transit.server.data.schema.CrimeAggregateColumns;
import vt.transit.server.data.schema.RailColumns;

public class MetroStopService {

	private static final Logger logger = Logger.getLogger(MetroStopService.class);
	
	public static MetroStop getClosestMetroStop(Connection conn, double lat, double lon) {
		
		return null;
	}
	
	public static List<MetroStop> getAllMetroStopsNearby(Connection conn, double lat, double lon, double distance) {
		String query = 
			"SELECT rail.gid, rail.code, rail.name, rail.line_code_1, rail.line_code_2, rail.station_together_1, ST_AsText(rail.location) as geom " +
			"FROM rail " +
			"WHERE ST_DWithin(rail.location::geography, ST_GeographyFromText('POINT(" + lon + " " + lat + ")'), " + distance + ") ";

		List<MetroStop> metroStops = new ArrayList<MetroStop>();

		try {
			Statement st = conn.createStatement();
			ResultSet rs = null;
			try {
				logger.info("Executing query:\n" + query);
				rs = st.executeQuery(query);
				while (rs.next()) {
					MetroStop metroStop = createMetroStop(rs);								
					metroStops.add(metroStop);
				}

			} finally {
				if (rs != null) {
					rs.close();
				}

				st.close();
			}
		} catch (Exception e) {
			logger.error("Exception while running query", e);
		} 

		logger.info(metroStops.size());
		return metroStops;
	}
	
	public static List<MetroStopCrime> getMetroStopsAndCrimeNearby(Connection conn, double lat, double lon, int count) {
		String query =
			"SELECT rail.gid, rail.code, rail.name, rail.line_code_1, rail.line_code_2, rail.station_together_1, ST_AsText(rail.location) as geom, metro_crime_aggregate.aggregate_array, metro_crime_aggregate.total, " +
			"ST_DISTANCE(ST_GeographyFromText('SRID=4326;POINT(" + lon + " " + lat + ")'), location) as dist " +
			"FROM rail " +
			"LEFT OUTER JOIN metro_crime_aggregate " +
			"ON rail.code = metro_crime_aggregate.code " +
			"ORDER BY dist asc " +
			"LIMIT " + count;
		
		List<MetroStopCrime> metroStopCrimes = new ArrayList<MetroStopCrime>();

		try {
			Statement st = conn.createStatement();
			ResultSet rs = null;
			try {
				logger.info("Executing query:\n" + query);
				rs = st.executeQuery(query);
				while (rs.next()) {
					MetroStopCrime metroCrime = createMetroStopCrime(rs);								
					metroStopCrimes.add(metroCrime);
				}

			} finally {
				if (rs != null) {
					rs.close();
				}

				st.close();
			}
		} catch (Exception e) {
			logger.error("Exception while running query", e);
		} 

		logger.info(metroStopCrimes.size());
		return metroStopCrimes;
	}
	
	private static MetroStop createMetroStop(ResultSet rs) throws SQLException {
		MetroStop metroStop = new MetroStop();
		
		metroStop.setGid(rs.getInt(RailColumns.gid));
		metroStop.setCode(rs.getString(RailColumns.code));
		metroStop.setName(rs.getString(RailColumns.name));
		metroStop.setLine_code_1(rs.getString(RailColumns.line_code_1));
		metroStop.setLine_code_2(rs.getString(RailColumns.line_code_2));
		metroStop.setStation_together_1(rs.getString(RailColumns.station_together_1));
		metroStop.setLocation(rs.getString("geom"));
		
		return metroStop;
	}
	
	private static MetroStopCrime createMetroStopCrime(ResultSet rs) throws SQLException {	
		MetroStopCrime metroCrime = new MetroStopCrime();		
		MetroStop metroStop = createMetroStop(rs);
		
		metroCrime.setMetroStop(metroStop);
		metroCrime.setCrimeAggregate(rs.getString(CrimeAggregateColumns.aggregate_array));
		metroCrime.setTotal(rs.getInt(CrimeAggregateColumns.total));
		metroCrime.setDistance(rs.getDouble("dist"));
		
		return metroCrime;
	}
}
